<?php
/**
 * Created by PhpStorm.
 * User: lixy
 * Date: 2016/12/14
 * Time: 16:26
 */

namespace app\models;


class Report
{
    /**
     * 获取某机构的流转订单列表
     * @param $institutionCode
     * @param $offset
     * @param $limit
     * @return array
     */
    public static function getFlowList2($institutionCode,  $offset, $limit, $start_time, $end_time,$origin_code,$result_type='count')
    {
        $order_statusSql=self::getInstitutionBillModelToOrderState($institutionCode);
        $order_origin_codeSql='';
        if (!empty($origin_code)) {
            $order_origin_codeSql =  ' AND o.origin_code = :origin_code ';
        }
        $sql_order="
            SELECT o.order_code t1,o.user_name t2,o.total_settlement_price t3
            ,(select ifnull(sum(total_fee * 0.01),0) from t_order_cash_payment_record r where o.order_code=r.order_code and r.status=1)  t4
            ,o.status_name t5,o.create_datetime t6
            FROM v_order_flow o WHERE o.institution_code=:institutionCode
             and SUBSTRING(o.create_datetime FROM 1 FOR 10) >=:start_time
            and SUBSTRING(o.create_datetime FROM 1 FOR 10) <=:end_time
            and $order_statusSql $order_origin_codeSql
        ";
       $sql_backOrder="
          select r.order_code t1,i.name t2,(-1 * r.settlement_price * r.quantity) t3,0 t4
            ,'退货' t5,r.create_datetime t6
            from (
				select t2.settlement_price,t0.*
					from t_back_goods_record t0,t_order_flow t1,t_order_flow_goods t2
					 where   t0.order_code=t1.order_code  and t1.id=t2.order_flow_id and t2.goods_code=t0.goods_code
					 and t1.institution_code=t0.institution_code
				) r
             left JOIN t_institution i on i.`code`=r.institution_code
             where   r.institution_code in
            (select code from t_institution where code_tree like (
            select concat(code_tree,'%') from t_institution where code=:institutionCode ))
            and SUBSTRING(r.create_datetime FROM 1 FOR 10) >=:start_time
              and SUBSTRING(r.create_datetime FROM 1 FOR 10) <=:end_time
       ";
        $sql="($sql_order) UNION all ($sql_backOrder) ";
        if ($result_type=='export') {
            $sql="($sql_order) UNION ($sql_backOrder) ";
        }
        if ($result_type=='count') {
            $exec_sql = "select count(1) from ($sql) as tt";
        }
        if ($result_type=='list') {
            $exec_sql = "select *,SUBSTRING(t1 FROM 1 FOR 18) t0 from ( $sql ) tt ORDER BY t0 desc,t6 desc LIMIT :offset, :limit";
        }
        if ($result_type=='export') {
            $exec_sql = "select t1,t4,SUBSTRING(t1 FROM 1 FOR 18) t0 from ( $sql ) tt group by t1 ORDER BY t0 desc LIMIT :offset, :limit";
        }
        $cmd = \Yii::$app->db->createCommand($exec_sql);
        $cmd->bindValue(':institutionCode', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $cmd->bindValue(':end_time', $end_time);
        if ($result_type=='list'|| $result_type=='export') {
            $cmd->bindValue(':offset', $offset);
            $cmd->bindValue(':limit', $limit);
        }

        if (!empty($origin_code)) {
            $cmd->bindValue(':origin_code', $origin_code);
        }

        if ($result_type=='count') {
            return $cmd->queryScalar();
        }
        if ($result_type=='list'|| $result_type=='export') {
//            echo $cmd->getRawSql();exit;
            return $cmd->queryAll();
        }
        return false;
    }

//账单模式：
//1：按订单生成（ForOrder）
//2：按发货生成（ForSend）
    static public function getInstitutionBillModelToOrderState($institutionCode){
        $sql='select bill_model from t_institution  where code=:institutionCode';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionCode', $institutionCode);
        $billModel=$cmd->queryScalar();
        if($billModel=='2'){
            return ' o.status not in (41,42) ';
        }else{
            return 'o.status in (30,31,32,33,40) ';
        }
   }
    static public function getSumInstitutionAccountRecordSum($institutionCode,$origin_code,$start_time,$end_time){
        if(empty($origin_code)==false){
            $institutionCode=$origin_code;
        }
        $dataSql='and SUBSTRING(create_datetime FROM 1 FOR 10) >=:start_time
          and SUBSTRING(create_datetime FROM 1 FOR 10) <=:end_time ';


        $sql="select IFNULL(sum(amount),0)  from t_institution_account_record where record_type in (4) $dataSql and institution_code=:institution_code";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $cmd->bindValue(':end_time', $end_time);
        $now_pay=$cmd->queryScalar();//现金结算总额

        $sql = 'select IFNULL(sum(amount),0)  from t_institution_account_record where record_type in (1,2,3,4,5) and create_datetime<=:start_time and institution_code=:institution_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $pre_yu = $cmd->queryScalar();//上期余额



        $sql="select IFNULL(sum(amount),0)  from t_institution_account_record where record_type in (1) $dataSql and institution_code=:institution_code";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $cmd->bindValue(':end_time', $end_time);
        $now_inc=$cmd->queryScalar();//本期机构上账

        $sql="select IFNULL(sum(amount),0) from t_institution_account_record where record_type in (2) $dataSql  and institution_code=:institution_code";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $cmd->bindValue(':end_time', $end_time);
        $now_dec=$cmd->queryScalar();//本期订单消费

        $sql="select IFNULL(sum(amount),0) from t_institution_account_record where record_type in (5) $dataSql  and institution_code=:institution_code";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':start_time', $start_time);
        $cmd->bindValue(':end_time', $end_time);
        $now_back=$cmd->queryScalar();//订单退款总金额
        $inst_account=self::getInstitution_account($institutionCode);
        return [
            'pre_yu'=>$pre_yu,
            'now_inc'=>$now_inc,
            'now_dec'=>$now_dec,
            'now_back'=>$now_back,
            'now_pay'=>$now_pay,
            'inst_account'=>$inst_account,
        ];
    }
    //
    static public function getBackGoodsRecord($order_code){
        $institution_code=self::getBackGoodsRecord_institutionCode($order_code);
        if($institution_code){
            $sql="select t0.*,t2.goods_name,t2.settlement_price,t2.selling_price
					from t_back_goods_record t0,t_order_flow t1,t_order_flow_goods t2
					 where   t0.order_code=t1.order_code
					 and t1.institution_code=t0.institution_code and
					  t0.institution_code=:institution_code
					 and t1.id=t2.order_flow_id and t2.goods_code=t0.goods_code and t0.order_code=:order_code";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':order_code', $order_code);
            $cmd->bindValue(':institution_code',$institution_code );
//        echo $cmd->getRawSql();exit;
            return $cmd->queryAll();
        }
        return [];
    }

    /**退单表查询机构编码
     * @param $order_code
     * @return false|null|string
     */
    static public function getBackGoodsRecord_institutionCode($order_code){
        $sql='select institution_code  from t_back_goods_record r   where r.order_code=:order_code ';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':order_code', $order_code);
        //echo $cmd->getRawSql();exit;
        return $cmd->queryScalar();
    }
    static public function getUserOrderInfo($order_code,$institutionCode){
        $sql='select o.*,og.goods_code,og.quantity,g.name as goods_name, g.settlement_price,og.selling_price
        from t_order o ,t_order_goods og,(select * from t_goods where institution_code=:institution_code) g
          where o.code=:order_code and og.order_id=o.id and g.code=og.goods_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':order_code', $order_code);
        $cmd->bindValue(':institution_code', $institutionCode);
        //echo $cmd->getRawSql();exit;
        return $cmd->queryAll();
    }

    /**获取机构账户信息
     * @param $institutionCode
     * @return array|false
     */
    static public function getInstitution_account($institutionCode){
        $sql='select *  from t_institution_account   where  institution_code=:institution_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        //echo $cmd->getRawSql();exit;
        return $cmd->queryOne();
    }
    static public function getOrderFlowByOrderCode($institutionCode,$orderCode){
        $sql='select *  from t_order_flow   where  institution_code=:institution_code and order_code=:order_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':order_code', $orderCode);
        //echo $cmd->getRawSql();exit;
        return $cmd->queryOne();
    }
}